using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SqlSugar;
using SqlSugarLearing.Model;
using UtilTools;

namespace SqlSugarLearing
{
    public class SqlTest_Company
    {
        public void SimpleTest001()
        {
            List<CRMOperator> list = DbHelper.Db.Queryable<CRMOperator>()
                                    .Where((a) => !string.IsNullOrEmpty(a.OPName) && a.OPType.Equals("B0006"))
                                    .OrderBy((a) => a.Id, OrderByType.Desc)
                                    .Take(5).ToList();
            PrintHelper.PrintList(list);
        }

        public void SimpleTest002()
        {
            string sortBy = "id";
            string sortType = "desc";
            var query = DbHelper.Db.Queryable<CRMOperator>()
                                    .Where((a) => !string.IsNullOrEmpty(a.OPName) && a.OPType.Equals("B0006"));

            OrderByType orderType = sortType == "desc" ? OrderByType.Desc : OrderByType.Asc;
            query = sortBy switch
            {
                "OPCompany" => query.OrderBy(a => a.OPCompany, orderType),
                _ => query.OrderBy(a => a.Id, orderType)
            };

            var list = query.Take(5).Select((o) => new { Id = o.Id }).ToList();
            PrintHelper.PrintList(list);
        }

        public void SimpleTest003()
        {
            var obj = DbHelper.Db.Queryable<CRMOperator>()
                                    .Where((a) => !string.IsNullOrEmpty(a.OPName) && a.OPType.Equals("B0006"))
                                    .OrderBy((a) => a.Id, OrderByType.Desc)
                                    .InSingle(17640);
            PrintHelper.PrintList(new List<CRMOperator>() { obj });
        }

        /// <summary>
        /// In条件查询
        /// </summary>
        public void SimpleTest004()
        {
            List<int> listIDs = new List<int>() { 17640, 17637 };
            List<CRMOperator> list = DbHelper.Db.Queryable<CRMOperator>()
                        .Where(a => !string.IsNullOrEmpty(a.OPName) && a.OPType.Equals("B0006"))
                        .Where(a => listIDs.Contains(a.Id))
                        .OrderBy((a) => a.Id, OrderByType.Desc)
                        .Take(5).ToList();
            PrintHelper.PrintList(list);
        }

        /// <summary>
        /// 联表查询
        /// </summary>
        public void LeftTest001()
        {
            var list = DbHelper.Db.Queryable<CRMOperator, CRMYTStaff>((a, b) => new JoinQueryInfos(
                JoinType.Left, a.OPCompany == b.staffID.ToString()))
             .AS<CRMYTStaff>("YTStaff")
             .Where((a) => !string.IsNullOrEmpty(a.OPName) && a.OPType.Equals("B0006"))
             .OrderBy((a) => a.Id, OrderByType.Desc)
             .Select((a, b) => new
             {
                 OId = a.Id,
                 OName = a.OPName,
                 YSId = b.staffID,
                 YSName = b.StaffName,
                 YSDepartment = b.StaffDepartMent
             }).Take(5).ToList();
            PrintHelper.PrintList(list);
        }

        /// <summary>
        /// 树查询
        /// </summary>
        public void TreeTest001()
        {
            var tree = DbHelper.Db.Queryable<YTDepartMent>().ToTree(it => it.Childs, it => it.DPID, 1000).Where(it => it.ValidFlag);

            List<YTDepartMent> list = tree.SelectMany(it => it.GetAllChildIds()).ToList();

            PrintHelper.PrintList(list);
        }

        /// <summary>
        /// 下划线测试
        /// </summary>
        public void TableUnderLineTest001()
        {
            var list = DbHelper.Db.Queryable<UserTest>().ToList();
            PrintHelper.PrintList(list);
        }
    }
}